Database management systems exist today that provide database developers with the ability to write program code that can be executed within the database environment by, for example, a runtime component integrated within the database environment. For example, MICROSOFT SQL SERVER is a comprehensive database management platform that provides extensive management and development tools, a powerful extraction, transformation, and loading (ETL) tool, business intelligence and analysis services, and other capabilities. Among other improvements, the MICROSOFT WINDOWS .NET Framework Common Language Runtime (CLR), a runtime environment, recently has been integrated into the SQL SERVER database.
The CLR is the heart of the MICROSOFT .NET Framework, and provides the execution environment for all .NET code. Thus, code that runs within the CLR is referred to as “managed code.” The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management and security. The CLR is now loaded by SQL SERVER upon the first invocation of a .NET routine.
In previous versions of SQL SERVER, database programmers were limited to using Transact-SQL when writing code on the server side. Transact-SQL is an extension of the Structured Query Language as defined by the International Standards Organization (ISO) and the American National Standards Institute (ANSI). Using Transact-SQL, database developers can create, modify and delete databases and tables, as well as insert, retrieve, modify and delete data stored in a database. Transact-SQL is specifically designed for direct structural data access and manipulation. While Transact-SQL excels at structural data access and management, it is not a full-fledged programming language as are VISUAL BASIC .NET and C#. For example, Transact-SQL does not support arrays, collections, for each loops, bit shifting or classes.
With the CLR integrated into the SQL SERVER database, database developers can now perform tasks that were impossible or difficult to achieve with Transact-SQL alone. Both VISUAL BASIC .NET and C# are modern programming languages offering full support for arrays, structured exception handling, and collections. Developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks using languages such as VISUAL BASIC .NET and C#. These programming languages offer object-oriented capabilities such as encapsulation, inheritance and polymorphism. Related code can be easily organized into classes and namespaces.
Managed code is better suited than Transact-SQL for number crunching and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Base Class Library (BCL), database developers have access to thousands of pre-built classes and routines which can be easily accessed from any stored procedure, trigger or user defined function.
Another benefit of managed code is type safety. Before managed code is executed, the CLR verifies that the code is safe. This process is known as “verification.” During verification, the CLR performs several checks to ensure that the code is safe to run. For example, the code is checked to ensure that no memory is read that has not be been written to. The CLR will also prevent buffer overflows.
When writing managed code, the deployment unit is called an assembly. An assembly is packaged as a dynamic link library (DLL). Managed DLL assemblies can be loaded into and hosted by SQL SERVER. The CREATE ASSEMBLY statement is used to register an assembly in the server. Here is an example:
CREATE ASSEMBLY YukonCLRFROM ‘C:\MyDBApp\YukonCLR.dll’The FROM clause specifies the pathname of the assembly to load.
Traditionally, database management systems, such as SQL SERVER, have supported built-in aggregate functions defined by the structured query language (SQL), such as COUNT, SUM, MAX, MIN, and AVG, that operate on a set of input scalar values and generate a single aggregate value from that set. Typically, in these systems, the only option users have to program the computation of an aggregate function is to write a stored procedure that creates a cursor over a set of rows and computes the aggregate function by iterating over groups of rows in that cursor. It would be desirable if users could create their own custom aggregate functions that would operate, from the standpoint of a user, as any of the built-in aggregate functions. The present invention provides such a capability.